# Frierson, Dylan Lab 7
# 1 
CREATE DATABASE IF NOT EXISTS Painters;
# 2
USE painters; 

DROP USER bob@localhost;
DROP USER jan@localhost;
DROP USER sal@localhost;
# 3
DROP VIEW IF EXISTS namesandjobs;
DROP VIEW IF EXISTS totalpay;
# 4
DROP INDEX ix_customer_fullname ON customer;
DROP INDEX ix_fks_job ON job;
DROP INDEX ix_fks_empjob ON empjob;
# 5
DROP TABLE customer;
DROP TABLE job;
DROP TABLE employee;
DROP TABLE empjob;
# 6
CREATE TABLE customer (
    custphone CHAR(12) UNIQUE,
    ctype ENUM('C', 'R'),
    clname VARCHAR(35) NOT NULL,
    cfname VARCHAR(15) NOT NULL,
    caddr VARCHAR(40),
    ccity VARCHAR(20),
    cstate CHAR(2) DEFAULT 'SC',
    PRIMARY KEY (custphone)
);
CREATE TABLE job (
    jobnum INT UNSIGNED NOT NULL AUTO_INCREMENT,
    custphone CHAR(12), 
    CONSTRAINT fk_custphone
    FOREIGN KEY (custphone) 
    REFERENCES customer (custphone),
    jobstartdate DATE,
    description TEXT CHECK (description <= 2000),
    amobilled DECIMAL(7,2),
    PRIMARY KEY (jobnum)
);
CREATE TABLE employee (
    essn CHAR(9) NOT NULL,
    elname VARCHAR(35) NOT NULL,
    efname VARCHAR(15) NOT NULL,
    ephone CHAR(12) UNIQUE,
    hrrate DECIMAL(5, 2) NOT NULL DEFAULT 15.75 CHECK (hrrate <= 100.00),
    PRIMARY KEY (essn)
);
CREATE TABLE empjob (
    essn CHAR(9),
    CONSTRAINT fk_essn
    FOREIGN KEY (essn) 
    REFERENCES employee (essn),
    jobnum INT UNSIGNED, 
    CONSTRAINT fk_jobnum
    FOREIGN KEY (jobnum) 
    REFERENCES job (jobnum),
    hrsperjob DECIMAL(6,2) CHECK (hrsperjob <= 500),
    PRIMARY KEY (essn, jobnum)
);
# 7
CREATE INDEX ix_customer_fullname ON customer (cfname, clname);
# 8
CREATE INDEX ix_fks_job ON job (custphone);
CREATE INDEX ix_fks_empjob ON empjob (essn, jobnum);
# 9
CREATE USER bob@localhost
IDENTIFIED BY 'userpass1';
GRANT ALL ON painters.* TO bob@localhost;
CREATE USER jan@localhost
IDENTIFIED BY 'userpass2';
GRANT SELECT ON painters.* TO jan@localhost;
CREATE USER sal@localhost
IDENTIFIED BY 'userpass3';
GRANT ALL ON painters.customer TO sal@localhost;
GRANT ALL ON painters.job TO sal@localhost;
GRANT SELECT ON painters.employee TO sal@localhost;
GRANT SELECT ON painters.empjob TO sal@localhost;
# 10
INSERT INTO customer VALUES ("(803)939-8989", "C", "Lee", "Daniel", "132 Bill Lane", "Aiken", "SC");
INSERT INTO customer VALUES ("(803)978-2380", "R", "Thing", "Nasty", "133 Bill Lane", "Aiken", "SC");
INSERT INTO customer VALUES ("(803)765-3721", "C", "Man", "Tall", "134 Bill Lane", "Aiken", "SC");

INSERT INTO job VALUES (1, "(803)939-8989", "2019-10-12", "Welder", "5000.00");
INSERT INTO job VALUES (2, "(803)978-2380", "2020-09-06", "HR", "3000.00");
INSERT INTO job VALUES (3, "(803)765-3721", "2018-01-30", "Security", "2500.00");

INSERT INTO empjob VALUES ("238854656", 1, "300.00");
INSERT INTO empjob VALUES ("458208943", 2, "200.00");
INSERT INTO empjob VALUES ("805392108", 3, "250.00");

INSERT INTO employee VALUES ("238854656", "Roberson", "Jim", "(803)568-3198", "20.00");
INSERT INTO employee VALUES ("458208943", "Went", "David", "(803)892-7521", "25.00");
INSERT INTO employee VALUES ("805392108", "Widner", "Bobby", "(803)459-3278", "16.00");
# 11
UPDATE customer SET ctype = "R"
WHERE ctype = "C";
UPDATE job SET jobstartdate = "2019-10-05"
WHERE jobstartdate = "2019-10-12";
UPDATE empjob SET hrsperjob = "350.00"
WHERE hrsperjob = "300.00";
UPDATE employee SET hrrate = "25.00"
WHERE hrrate = "20.00"; 
# 12 
DELETE FROM customer WHERE clname = "Lee";
DELETE FROM job WHERE description = "Welder";
DELETE FROM empjob WHERE hrsperjob = "350.00";
DELETE FROM employee WHERE hrrate = "16.00";
# 13 
CREATE VIEW namesandjobs AS 
SELECT CONCAT(cfname, " ", clname) AS "Customer Full Name", job.jobnum, jobstartdate, CONCAT(efname, " ", elname) AS "Employee Full Name"
FROM customer, job, empjob, employee
WHERE customer.custphone = job.custphone AND job.jobnum = empjob.jobnum AND empjob.essn = employee.essn;
# 14
SELECT * FROM namesandjobs;
# 15 
CREATE VIEW totalpay AS 
SELECT CONCAT(efname, ' ',elname) AS 'Full Name', (hrrate * hrsperjob) AS 'Total Pay'
FROM employee, empjob
WHERE employee.essn = empjob.essn;
# 16 
SELECT * FROM totalpay;